/*
 * Copyright 2013 Hongxin Telecommunication Technologies Co, Ltd.,
 * Wuhan, Hubei, China. All rights reserved.
 */
package com.hxrd.interaction;

import com.hxrd.data.Device;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import static com.hxrd.interaction.InterActive.*;
import java.sql.SQLException;
import java.util.Map;

/**
 *
 * @author piqianghua Created on 2017-1-5, 10:15:59
 */
public class DBManage
{
	private static Log log = LogFactory.getLog(DBManage.class);
	private static final String user = "sa";
	private static final String password = "123456";
	private static final String driver = "org.h2.Driver";
	private static final String outpath
			= "导出文件.xls";

	//获取数据库连接
	public static Connection getCon()
	{
		Connection con=null ;
		try
		{
			Class.forName(driver).newInstance();
			String url = System.getProperty("user.dir");
			con = DriverManager.
					getConnection("jdbc:h2:file:" + url + "\\test;IFEXISTS=true", user, password);
		}
		catch(Exception e)
		{
			System.err.println("数据库连接失败！");
			log.info("数据库连接失败！");
			log.info(getTrace(e));
			System.exit(0);
		}
		return con;
	}

	//批量导入设备
	public int insertDB(HashMap<Integer, Device> listData) throws SQLException
	{
		String sql;
		int k = 0;
		Connection con = getCon();
		Statement stm = con.createStatement();
		ResultSet rs = null;
		Device item;
		for(Map.Entry<Integer, Device> entry : listData.entrySet())
		{
			item = entry.getValue();
			sql
					= "insert into DEVICE(areaID, venderID,modelID, numberId,ip,masterId,nasip,username,password,community,port,version) values('" + item.
					getAreaId() + "','" + item.getVenderId() + "','" + item.
					getModelId() + "','" + item.getNumberId() + "','" + item.
					getIp()
					+ "','" + item.
					getMasterId() + "','" + item.getNasip() + "','" + item.
					getUsername() + "','"
					+ item.getPassword() + "','" + item.
					getCommunity() + "','"
					+ item.getPort() + "','" + item.getVersion() + "')";
			try
			{
				k = stm.executeUpdate(sql);
			}
			catch(Exception e)
			{
				log.info(getTrace(e));
			}
		}
		close(rs, stm, con);
		return k;
	}

	//查询设备表并显示
	public void QueryDB(String ss)
	{
		Connection con = null;
		Statement stm = null;
		ResultSet rs = null;
		String sql;
		int count = 0;
		try
		{
			con = getCon();
			stm = con.createStatement();
			if(ss.length() == 0 || ss.equals(null))
			{
				sql
						= "select DISTRICT.areaID AS 地市, FACTORY.venderID AS 厂家,DEVICENO.modelID AS 设备型号,numberId AS AC编号,ip AS "
						+ "AC地址,masterId AS 主备情况,nasip,username AS 用户名,password AS 密码,"
						+ "community AS 共同体,port AS 端口,version AS SNMP版本 from DEVICE join DISTRICT on DEVICE.areaId=DISTRICT.ID join "
						+ "FACTORY on DEVICE.venderId=FACTORY.ID join DEVICENO on DEVICE.modelId=DEVICENO.ID ";
			}
			else
			{
				sql
						= "select DISTRICT.areaID AS 地市, FACTORY.venderID AS 厂家,DEVICENO.modelID AS 设备型号,numberId AS AC编号,ip AS "
						+ "AC地址,masterId AS 主备情况,nasip,username AS 用户名,password AS 密码,"
						+ "community AS 共同体,port AS 端口,version AS SNMP版本 from DEVICE join DISTRICT on DEVICE.areaId=DISTRICT.ID join "
						+ "FACTORY on DEVICE.venderId=FACTORY.ID join DEVICENO on DEVICE.modelId=DEVICENO.ID  "
						+ "where ip like '%" + ss.trim() + "%'";
			}
			rs = stm.executeQuery(sql);
			ResultSetMetaData metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();
			for(int i = 1; i <= columnCount; i++)
			{
				System.out.printf(metaData.getColumnLabel(i) + "  ");
			}
			System.out.println();
			while(rs.next())
			{
				for(int i = 1; i <= columnCount; i++)
				{
					if(i == 6)
					{
						if(Integer.valueOf(rs.getString(i)) == 1)
						{
							System.out.print("主" + "  ");
						}
						else if(Integer.valueOf(rs.getString(i)) == 2)
						{
							System.out.print("备" + "  ");
						}
					}
					else
					{
						System.out.print(rs.getString(i) + "  ");
					}
				}
				System.out.println();
				count++;
			}
			System.out.println("查询出设备记录" + count + "条");
		}
		catch(Exception e)
		{
			log.info(getTrace(e));
		}
		close(rs, stm, con);
	}

	//获取设备链表
	public ArrayList<Device> getDB()
	{
		ArrayList<Device> ad = new ArrayList<Device>();
		Connection con = null;
		Statement stm = null;
		ResultSet rs = null;
		String sql;
		try
		{
			con = getCon();
			stm = con.createStatement();
			sql = "select *from DEVICE ";
			rs = stm.executeQuery(sql);
			while(rs.next())
			{
				Device item = new Device();
				item.setId(rs.getInt("ID"));
				item.setAreaId(rs.getInt("areaId"));
				item.setVenderId(rs.getInt("venderId"));
				item.setModelId(rs.getInt("modelId"));
				item.setNumberId(rs.getString("numberId"));
				item.setIp(rs.getString("ip"));
				item.setMasterId(rs.getInt("masterId"));
				item.setNasip(rs.getString("nasip"));
				item.setUsername(rs.getString("username"));
				item.setPassword(rs.getString("password"));
				item.setCommunity(rs.getString("community"));
				item.setPort(rs.getInt("port"));
				item.setVersion(rs.getInt("version"));
				ad.add(item);
			}

		}
		catch(Exception e)
		{
			log.info(getTrace(e));
		}
		close(rs, stm, con);
		return ad;
	}

	//导出设备
	public void ExportDB()
	{
		// 创建工作文档对象	
		HSSFWorkbook wb = new HSSFWorkbook();
		// 创建sheet对象
		HSSFSheet sheet1 = wb.createSheet("sheet1");
		Connection con = null;
		Statement stm = null;
		ResultSet rs = null;
		String sql;
		try
		{
			con = getCon();
			stm = con.createStatement();
			sql
					= "select DISTRICT.areaID AS 地市, FACTORY.venderID AS 厂家,DEVICENO.modelID AS 设备型号,numberId AS AC编号,ip AS "
					+ "AC地址,masterId AS 主备情况,nasip,username AS 用户名,password AS 密码,"
					+ "community AS 共同体,port AS 端口,version AS SNMP版本 from DEVICE join DISTRICT on DEVICE.areaId=DISTRICT.ID join "
					+ "FACTORY on DEVICE.venderId=FACTORY.ID join DEVICENO on DEVICE.modelId=DEVICENO.ID ";
			rs = stm.executeQuery(sql);
			ResultSetMetaData metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();
			HSSFRow row0 = sheet1.createRow(0);
			for(int i = 1; i <= columnCount; i++)
			{
				row0.createCell(i - 1).setCellValue(metaData.getColumnLabel(i));
				sheet1.setColumnWidth(i - 1, 5000);
			}
			int j = 1;
			while(rs.next())
			{
				HSSFRow row = sheet1.createRow(j);
				for(int i = 1; i <= columnCount; i++)
				{
					if(i == 6)
					{
						if(Integer.valueOf(rs.getString(i)) == 1)
						{
							row.createCell(i - 1).setCellValue("主");
						}
						else if(Integer.valueOf(rs.getString(i)) == 2)
						{
							row.createCell(i - 1).setCellValue("备");
						}
					}
					else
					{
						row.createCell(i - 1).setCellValue(rs.getString(i));
					}
				}
				j++;
			}
			// 创建文件流
			OutputStream stream = new FileOutputStream(outpath);
			String fileType = outpath.
					substring(outpath.lastIndexOf(".") + 1, outpath.length());
			if(outpath == null || !fileType.equals("xls"))
			{
				System.err.println(" 导出路径异常，请检查！'");
				log.error("导出路径异常，请检查！");
			}
			else
			{
				// 写入数据
				wb.write(stream);
				System.out.println("导出设备到 '" + outpath + "' 成功！");

			}
			// 关闭文件流
			stream.close();
		}
		catch(Exception e)
		{
			System.err.println(" 导出异常！'");
			log.error("导出异常！");
			log.info(getTrace(e));
		}
		close(rs, stm, con);
	}

	//增加、删除、清空设备
	public void ExecuteDB(String sql, int user)
	{
		Connection con = null;
		Statement stm = null;
		ResultSet rs = null;
		try
		{
			con = getCon();
			stm = con.createStatement();
			int k = stm.executeUpdate(sql);
			if(k > 0)
			{
				switch(user)
				{
					case 2:
						System.out.println("增加设备成功！");
						break;
					case 3:
						System.out.println("删除设备成功！");
						break;
					case 6:
						System.out.println("清空设备成功！");
						break;
					default:
						break;
				}
			}
			else
			{
				System.out.println("删除0条设备！");
			}

		}
		catch(Exception e)
		{
			log.info(getTrace(e));
		}
		close(rs, stm, con);
	}

	// 返回参数Map
	public HashMap<Integer, HashMap<Integer, String>> QueryDB()
	{
		HashMap<Integer, HashMap<Integer, String>> mapdata
				= new HashMap<Integer, HashMap<Integer, String>>();
		HashMap<Integer, String> data1 = new HashMap<Integer, String>();
		HashMap<Integer, String> data2 = new HashMap<Integer, String>();
		HashMap<Integer, String> data3 = new HashMap<Integer, String>();
		Connection con = null;
		Statement stm1 = null;
		Statement stm2 = null;
		Statement stm3 = null;
		String sql1 = null;
		String sql2 = null;
		String sql3 = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		ResultSet rs3 = null;
		sql1 = "select* from DISTRICT";
		sql2 = "select* from FACTORY";
		sql3 = "select* from DEVICENO ";
		try
		{
			con = getCon();
			stm1 = con.createStatement();
			stm2 = con.createStatement();
			stm3 = con.createStatement();
			rs1 = stm1.executeQuery(sql1);
			rs2 = stm2.executeQuery(sql2);
			rs3 = stm3.executeQuery(sql3);
			while(rs1.next())
			{
				data1.put(rs1.getInt("ID"), rs1.getString("areaId"));
			}
			while(rs2.next())
			{

				data2.put(rs2.getInt("ID"), rs2.getString("venderId"));
			}
			while(rs3.next())
			{
				data3.put(rs3.getInt("ID"), rs3.getString("modelId"));
			}
			mapdata.put(1, data1);
			mapdata.put(2, data2);
			mapdata.put(3, data3);
		}
		catch(Exception e)
		{
			log.info(getTrace(e));
		}
		close(rs1, stm1, con);
		close(rs2, stm2, con);
		close(rs3, stm3, con);
		return mapdata;
	}

	//IP地址校验
	public boolean ACDEFINE(String ip)
	{
		if(ip.trim().isEmpty())
		{
			System.out.println("IP地址为空，请重新输入：");
			return true;
		}
		if(!Regex(ip, 2))
		{
			return true;
		}
		Connection con = null;
		Statement stm = null;
		ResultSet rs = null;
		boolean flag = false;
		try
		{
			con = getCon();
			stm = con.createStatement();
			String sql = "select ip from DEVICE";
			rs = stm.executeQuery(sql);
			ResultSetMetaData metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();
			while(rs.next())
			{
				for(int i = 1; i <= columnCount; i++)
				{
					if(rs.getString(i).trim().equals(ip.trim()))
					{
						flag = true;
						System.out.println("IP地址重复，请重新输入");
					}
					break;
				}
			}
		}
		catch(Exception e)
		{
			log.info(getTrace(e));
		}
		close(rs, stm, con);
		return flag;
	}

	//IP地址正则匹配
	public boolean Regex(String ip, int i)
	{
		String regex = "^(1\\d{2}|2[0-4]\\d|25[0-5]|[1-9]\\d|[1-9])\\."
				+ "(1\\d{2}|2[0-4]\\d|25[0-5]|[1-9]\\d|\\d)\\."
				+ "(1\\d{2}|2[0-4]\\d|25[0-5]|[1-9]\\d|\\d)\\."
				+ "(1\\d{2}|2[0-4]\\d|25[0-5]|[1-9]\\d|\\d)$";
		//判断IP地址是否与正则表达式匹配
		if(ip.matches(regex))
		{
			//返回判断信息
			return true;
		}
		else
		{
			if(i == 2)
			{
				System.out.println("IP地址非法，请重新输入！");
			}
			return false;
		}
	}

	//插入数据
	public int ParamInsert(String sql1, String sql2)
	{
		Connection con = null;
		Statement stm = null;
		Statement stmQ = null;
		ResultSet rs = null;
		int id = 0;
		try
		{
			con = getCon();
			stm = con.createStatement();
			stmQ = con.createStatement();
			int k = stm.executeUpdate(sql1);
			rs = stmQ.executeQuery(sql2);
			while(rs.next())
			{
				id = rs.getInt("ID");
			}

		}

		catch(Exception e)
		{
			log.info(getTrace(e));
		}
		close(rs, stm, con);
		return id;
	}

	//判断参数重复
	public boolean ParamRepeat(String Param, int order)
	{
		Connection con = null;
		Statement stm = null;
		boolean flag = false;
		ResultSet rs = null;
		try
		{
			con = getCon();
			stm = con.createStatement();
			String sql = null;
			switch(order)
			{
				case 1:
					sql = "select areaId from DISTRICT";
					break;
				case 2:
					sql = "select venderId from FACTORY";
					break;
				case 3:
					sql = "select modelId from DEVICENO";
					break;
				default:
					break;
			}
			rs = stm.executeQuery(sql);
			ResultSetMetaData metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();
			while(rs.next())
			{
				for(int i = 1; i <= columnCount; i++)
				{
					if(rs.getString(i).trim().equals(Param.trim()))
					{
						flag = true;
					}
					break;
				}
			}
		}

		catch(Exception e)
		{
			log.info(getTrace(e));
		}
		close(rs, stm, con);
		return flag;
	}

	//关闭相关连接
	public void close(ResultSet rs, Statement stm, Connection con)
	{
		if(rs != null)
		{
			try
			{
				rs.close();
			}
			catch(Exception e)
			{
				log.info(getTrace(e));
			}
		}
		if(stm != null)
		{
			try
			{
				stm.close();
			}
			catch(Exception e)
			{
				log.info(getTrace(e));
			}
		}
		if(con != null)
		{
			try
			{
				con.close();
			}
			catch(Exception e)
			{
				log.info(getTrace(e));
			}
		}
	}
}
